﻿using System.Data;
using IMClient.Model.DataBase;
using Microsoft.Data.Sqlite;

namespace IMClient.Cache.Repositories
{
    public sealed class GroupCacheRepository
    {
        private static readonly GroupCacheRepository _instance = new();

        private readonly DbContext _dbContext;
        private readonly SqliteConnection _connection;

        // 私有构造函数，防止外部实例化
        private GroupCacheRepository()
        {
            _dbContext = DbContext.Instance;
            _connection = _dbContext.GetConnection();
        }

        // 单例实例
        public static GroupCacheRepository Instance => _instance;

        public async Task CacheGroupAsync(string userId, GroupDataBase group)
        {
            await EnsureConnectionOpenAsync();

            var command = _connection.CreateCommand();
            command.CommandText = @"
                INSERT INTO group_cache (user_id, group_id, group_name, group_avatar, creator, last_update_time)
                VALUES (@userId, @groupId, @groupName, @groupAvatar, @creator, @lastUpdateTime)
                ON CONFLICT(user_id, group_id) 
                DO UPDATE SET 
                    group_name = @groupName,
                    group_avatar = @groupAvatar,
                    creator = @creator,
                    last_update_time = @lastUpdateTime";

            command.Parameters.AddWithValue("@userId", userId);
            command.Parameters.AddWithValue("@groupId", group.GroupId);
            command.Parameters.AddWithValue("@groupName", group.GroupName);
            command.Parameters.AddWithValue("@groupAvatar", group.GroupAvatar);
            command.Parameters.AddWithValue("@creator", group.Creator);
            command.Parameters.AddWithValue("@lastUpdateTime", group.LastUpdateTime.ToString("yyyy-MM-dd HH:mm:ss"));

            await command.ExecuteNonQueryAsync();
        }

        public async Task CacheGroupsAsync(string userId, IEnumerable<GroupDataBase> groups)
        {
            await EnsureConnectionOpenAsync(); // 确保连接已打开

            using var transaction = await _connection.BeginTransactionAsync();
            try
            {
                foreach (var group in groups)
                {
                    await CacheGroupAsync(userId, group);
                }
                await transaction.CommitAsync();
            }
            catch
            {
                await transaction.RollbackAsync();
                throw;
            }
        }

        public async Task<List<GroupDataBase>> GetCachedGroupsAsync(string userId)
        {
            await EnsureConnectionOpenAsync();

            var groups = new List<GroupDataBase>();
            var command = _connection.CreateCommand();
            command.CommandText = @"
                SELECT group_id, group_name, group_avatar, creator, last_update_time 
                FROM group_cache 
                WHERE user_id = @userId";
            command.Parameters.AddWithValue("@userId", userId);

            using var reader = await command.ExecuteReaderAsync();
            while (await reader.ReadAsync())
            {
                groups.Add(new GroupDataBase
                {
                    UserId = userId,
                    GroupId = reader.GetString(0),
                    GroupName = reader.GetString(1),
                    GroupAvatar = reader.GetString(2),
                    Creator = reader.GetString(3),
                    LastUpdateTime = DateTime.Parse(reader.GetString(4))
                });
            }

            return groups;
        }

        public async Task UpdateGroupAvatarAsync(string userId, string groupId, string newAvatar)
        {
            await EnsureConnectionOpenAsync();

            var command = _connection.CreateCommand();
            command.CommandText = @"
            UPDATE group_cache
            SET group_avatar = @newAvatar,
                last_update_time = @lastUpdateTime
            WHERE user_id = @userId AND group_id = @groupId";

            command.Parameters.AddWithValue("@newAvatar", newAvatar);
            command.Parameters.AddWithValue("@lastUpdateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            command.Parameters.AddWithValue("@userId", userId);
            command.Parameters.AddWithValue("@groupId", groupId);

            await command.ExecuteNonQueryAsync();
        }

        public async Task UpdateGroupNameAsync(string userId, string groupId, string newName)
        {
            await EnsureConnectionOpenAsync();

            var command = _connection.CreateCommand();
            command.CommandText = @"
            UPDATE group_cache
            SET group_name = @newName,
                last_update_time = @lastUpdateTime
            WHERE user_id = @userId AND group_id = @groupId";

            command.Parameters.AddWithValue("@newName", newName);
            command.Parameters.AddWithValue("@lastUpdateTime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
            command.Parameters.AddWithValue("@userId", userId);
            command.Parameters.AddWithValue("@groupId", groupId);

            await command.ExecuteNonQueryAsync();
        }

        // 确保数据库连接已打开
        private async Task EnsureConnectionOpenAsync()
        {
            if (_connection.State != ConnectionState.Open)
            {
                await _connection.OpenAsync();
            }
        }
    }
}
